package app.beit.fintrack.utils;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import app.beit.fintrack.bean.HospitalBean;
import app.beit.fintrack.bean.summary.SummaryRepDataBean;
import app.beit.fintrack.controller.bean.SummaryRepSessionBean;
import app.beit.fintrack.utils.constr.SessionConstr;

public class WriteExcelByRep extends AbstractExcelView {

	@SuppressWarnings("unchecked")
	@Override
	protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		final SummaryRepSessionBean stsb = (SummaryRepSessionBean) request.getSession().getAttribute(SessionConstr.SESSION_SUMMARY_TRACK);
		final List<SummaryRepDataBean> listSummaryDetails = (List<SummaryRepDataBean>) model.get("listSummaryDetails");
		this.buildAllStatus(listSummaryDetails, workbook, stsb);
	}
	
	/**
	 * create xls from page summary track station
	 * @param listSummaryDetails
	 * @param workbook
	 * @param stsb
	 */
	private void buildAllStatus(final List<SummaryRepDataBean> listSummaryDetails, final HSSFWorkbook workbook, final SummaryRepSessionBean stsb){
		final HospitalBean hospitalBean = stsb.getHospital();
		String hCode = "";
		if(hospitalBean != null){
			hCode = hospitalBean.getHospitalCode();
			if(hCode != null && hCode.length() > 0){
				final int len = hCode.length();
				hCode = hCode.substring(len - 5, len);
			}
		}
        // create a new Excel sheet
        final HSSFSheet sheet = workbook.createSheet(hCode);
        sheet.setDefaultColumnWidth(15);
        
        int rowNumber = 0;
        final CellStyle styleHeaderCenter = this.styleCenterWithBorderHeader(workbook);

	    final CellStyle styleDataCenter = this.styleCenterWithBorder(workbook);
	    final CellStyle styleDataLeft = this.styleLeftWithBorder(workbook);
	    final CellStyle styleDataRight = this.styleRightWithBorder(workbook);
        
        // create header row
        final HSSFRow headerRow0 = sheet.createRow(rowNumber);
        final HSSFRow headerRow1 = sheet.createRow(rowNumber+1);
        final HSSFRow headerRow2 = sheet.createRow(rowNumber+2);
        final HSSFRow headerRow3 = sheet.createRow(rowNumber+3);
        
        int col = 0;
        
        //Row 0
        headerRow0.createCell(col).setCellValue("\u0E25\u0E33\u0E14\u0E31\u0E1A");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("Rep No.");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("Tran Id");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("Eclaim No");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("HN");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);
        
        headerRow0.createCell(col).setCellValue("AN");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("PID");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("OP/IP");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("\u0E0A\u0E37\u0E48\u0E2D-\u0E19\u0E32\u0E21\u0E2A\u0E01\u0E38\u0E25");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("\u0E27\u0E31\u0E19\u0E17\u0E35\u0E48\u0E40\u0E02\u0E49\u0E32\u0E23\u0E31\u0E1A\u0E01\u0E32\u0E23\u0E23\u0E31\u0E01\u0E29\u0E32");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("\u0E27\u0E31\u0E19\u0E17\u0E35\u0E48\u0E08\u0E33\u0E2B\u0E19\u0E48\u0E32\u0E22");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        int c = col;
        col += 2;
        headerRow0.createCell(c).setCellValue("\u0E40\u0E07\u0E34\u0E19\u0E40\u0E14\u0E37\u0E2D\u0E19");
        headerRow0.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 2, c, c+1, workbook, sheet);
        headerRow3.createCell(c).setCellValue("\u0E23\u0E49\u0E2D\u0E22\u0E25\u0E30(%)");
        headerRow3.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c, c, workbook, sheet);
        headerRow3.createCell(c+1).setCellValue("\u0E08\u0E33\u0E19\u0E27\u0E19\u0E40\u0E07\u0E34\u0E19(\u0E1A\u0E32\u0E17)");
        headerRow3.getCell(c+1).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+1, c+1, workbook, sheet);

        headerRow0.createCell(col).setCellValue("\u0E40\u0E23\u0E35\u0E22\u0E01\u0E40\u0E01\u0E47\u0E1A (\u0E1A\u0E32\u0E17)");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("\u0E2A\u0E16\u0E32\u0E19\u0E30");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("\u0E04\u0E48\u0E32 \u0E1E\u0E23\u0E1A. (\u0E1A\u0E32\u0E17)");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("\u0E0A\u0E14\u0E40\u0E0A\u0E22 (\u0E1A\u0E32\u0E17)");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        headerRow0.createCell(col).setCellValue("\u0E2A\u0E48\u0E27\u0E19\u0E15\u0E48\u0E32\u0E07\u0E08\u0E32\u0E01\u0E01\u0E32\u0E23\u0E40\u0E23\u0E35\u0E22\u0E01\u0E40\u0E01\u0E47\u0E1A (\u0E1A\u0E32\u0E17)");
        headerRow0.getCell(col).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 3, col, col++, workbook, sheet);

        c = col;
        col += 2;
        headerRow0.createCell(c).setCellValue("OP REFER(NON HC)");
        headerRow0.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 2, c, c+1, workbook, sheet);
        headerRow3.createCell(c).setCellValue("OP REFER HMAIN");
        headerRow3.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c, c, workbook, sheet);
        headerRow3.createCell(c+1).setCellValue("OP REFER NHSO");
        headerRow3.getCell(c+1).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+1, c+1, workbook, sheet);

        c = col;
        col += 9;
        headerRow0.createCell(c).setCellValue("\u0E01\u0E2D\u0E07\u0E17\u0E38\u0E19 (\u0E1A\u0E32\u0E17)");
        headerRow0.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber + 1, c, c+8, workbook, sheet);
        headerRow2.createCell(c).setCellValue("HC");
        headerRow2.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c, c, workbook, sheet);
        headerRow3.createCell(c).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c, c, workbook, sheet);
        headerRow2.createCell(c+1).setCellValue("AE");
        headerRow2.getCell(c+1).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+1, c+1, workbook, sheet);
        headerRow3.createCell(c+1).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+1).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+1, c+1, workbook, sheet);
        headerRow2.createCell(c+2).setCellValue("INST");
        headerRow2.getCell(c+2).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+2, c+2, workbook, sheet);
        headerRow3.createCell(c+2).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+2).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+2, c+2, workbook, sheet);
        headerRow2.createCell(c+3).setCellValue("IP");
        headerRow2.getCell(c+3).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+3, c+3, workbook, sheet);
        headerRow3.createCell(c+3).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+3).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+3, c+3, workbook, sheet);
        headerRow2.createCell(c+4).setCellValue("DMIS");
        headerRow2.getCell(c+4).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+4, c+4, workbook, sheet);
        headerRow3.createCell(c+4).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+4).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+4, c+4, workbook, sheet);
        headerRow2.createCell(c+5).setCellValue("OP");
        headerRow2.getCell(c+5).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+5, c+5, workbook, sheet);
        headerRow3.createCell(c+5).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+5).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+5, c+5, workbook, sheet);
        headerRow2.createCell(c+6).setCellValue("Prior Authorize");
        headerRow2.getCell(c+6).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+6, c+6, workbook, sheet);
        headerRow3.createCell(c+6).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+6).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+6, c+6, workbook, sheet);
        headerRow2.createCell(c+7).setCellValue("DRUG");
        headerRow2.getCell(c+7).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+7, c+7, workbook, sheet);
        headerRow3.createCell(c+7).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+7).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+7, c+7, workbook, sheet);
        headerRow2.createCell(c+8).setCellValue("ON TOP");
        headerRow2.getCell(c+8).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+8, c+8, workbook, sheet);
        headerRow3.createCell(c+8).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+8).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c+8, c+8, workbook, sheet);
        ////////////////////////////////////////////////////////////////////

        //Appeal Header
        c = col;
        col += 23;
        headerRow0.createCell(c).setCellValue("\u0E02\u0E49\u0E2D\u0E21\u0E39\u0E25\u0E2D\u0E38\u0E17\u0E18\u0E23\u0E13\u0E4C");
        headerRow0.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber, rowNumber, c, c+23, workbook, sheet);
        headerRow1.createCell(c).setCellValue("\u0E02\u0E49\u0E2D\u0E21\u0E39\u0E25\u0E40\u0E14\u0E34\u0E21");
        headerRow1.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 1, rowNumber + 1, c, c+10, workbook, sheet);
        headerRow1.createCell(c+11).setCellValue("\u0E02\u0E49\u0E2D\u0E21\u0E39\u0E25\u0E43\u0E2B\u0E21\u0E48");
        headerRow1.getCell(c+11).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 1, rowNumber + 1, c+11, c+21, workbook, sheet);
        headerRow1.createCell(c+22).setCellValue("\u0E0A\u0E14\u0E40\u0E0A\u0E22\u0E2A\u0E38\u0E17\u0E18\u0E34");
        headerRow1.getCell(c+22).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 1, rowNumber + 1, c+22, c+23, workbook, sheet);
        
        //Old Appeal
        headerRow2.createCell(c).setCellValue("\u0E40\u0E23\u0E35\u0E22\u0E01\u0E40\u0E01\u0E47\u0E1A(\u0E1A\u0E32\u0E17)");
        headerRow2.getCell(c).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 3, c, c, workbook, sheet);
        headerRow2.createCell(c+1).setCellValue("HC");
        headerRow2.getCell(c+1).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+1, c+1, workbook, sheet);
        headerRow3.createCell(c+1).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+1).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+1, c+1, workbook, sheet);
        headerRow2.createCell(c+2).setCellValue("AE");
        headerRow2.getCell(c+2).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+2, c+2, workbook, sheet);
        headerRow3.createCell(c+2).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+2).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+2, c+2, workbook, sheet);
        headerRow2.createCell(c+3).setCellValue("INST");
        headerRow2.getCell(c+3).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+3, c+3, workbook, sheet);
        headerRow3.createCell(c+3).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+3).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+3, c+3, workbook, sheet);
        headerRow2.createCell(c+4).setCellValue("IP");
        headerRow2.getCell(c+4).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+4, c+4, workbook, sheet);
        headerRow3.createCell(c+4).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+4).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+4, c+4, workbook, sheet);
        headerRow2.createCell(c+5).setCellValue("DMIS");
        headerRow2.getCell(c+5).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+5, c+5, workbook, sheet);
        headerRow3.createCell(c+5).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+5).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+5, c+5, workbook, sheet);
        headerRow2.createCell(c+6).setCellValue("OP");
        headerRow2.getCell(c+6).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+6, c+6, workbook, sheet);
        headerRow3.createCell(c+6).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+6).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+6, c+6, workbook, sheet);
        headerRow2.createCell(c+7).setCellValue("Prior Authorize");
        headerRow2.getCell(c+7).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+7, c+7, workbook, sheet);
        headerRow3.createCell(c+7).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+7).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+7, c+7, workbook, sheet);
        headerRow2.createCell(c+8).setCellValue("DRUG");
        headerRow2.getCell(c+8).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+8, c+8, workbook, sheet);
        headerRow3.createCell(c+8).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+8).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+8, c+8, workbook, sheet);
        headerRow2.createCell(c+9).setCellValue("ON TOP");
        headerRow2.getCell(c+9).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+9, c+9, workbook, sheet);
        headerRow3.createCell(c+9).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+9).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+9, c+9, workbook, sheet);
        headerRow2.createCell(c+10).setCellValue("\u0E08\u0E48\u0E32\u0E22\u0E0A\u0E14\u0E40\u0E0A\u0E22");
        headerRow2.getCell(c+10).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 3, c+10, c+10, workbook, sheet);
        ///////////////////////////////////////////////////////////////////
        
        //New Appeal
        headerRow2.createCell(c+11).setCellValue("\u0E40\u0E23\u0E35\u0E22\u0E01\u0E40\u0E01\u0E47\u0E1A(\u0E1A\u0E32\u0E17)");
        headerRow2.getCell(c+11).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 3, c+11, c+11, workbook, sheet);
        headerRow2.createCell(c+12).setCellValue("HC");
        headerRow2.getCell(c+12).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+12, c+12, workbook, sheet);
        headerRow3.createCell(c+12).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+12).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+12, c+12, workbook, sheet);
        headerRow2.createCell(c+13).setCellValue("AE");
        headerRow2.getCell(c+13).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+13, c+13, workbook, sheet);
        headerRow3.createCell(c+13).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+13).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+13, c+13, workbook, sheet);
        headerRow2.createCell(c+14).setCellValue("INST");
        headerRow2.getCell(c+14).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+14, c+14, workbook, sheet);
        headerRow3.createCell(c+14).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+14).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+14, c+14, workbook, sheet);
        headerRow2.createCell(c+15).setCellValue("IP");
        headerRow2.getCell(c+15).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+15, c+15, workbook, sheet);
        headerRow3.createCell(c+15).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+15).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+15, c+15, workbook, sheet);
        headerRow2.createCell(c+16).setCellValue("DMIS");
        headerRow2.getCell(c+16).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+16, c+16, workbook, sheet);
        headerRow3.createCell(c+16).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+16).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+16, c+16, workbook, sheet);
        headerRow2.createCell(c+17).setCellValue("OP");
        headerRow2.getCell(c+17).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+17, c+17, workbook, sheet);
        headerRow3.createCell(c+17).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+17).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+17, c+17, workbook, sheet);
        headerRow2.createCell(c+18).setCellValue("Prior Authorize");
        headerRow2.getCell(c+18).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+18, c+18, workbook, sheet);
        headerRow3.createCell(c+18).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+18).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+18, c+18, workbook, sheet);
        headerRow2.createCell(c+19).setCellValue("DRUG");
        headerRow2.getCell(c+19).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+19, c+19, workbook, sheet);
        headerRow3.createCell(c+19).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+19).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+19, c+19, workbook, sheet);
        headerRow2.createCell(c+20).setCellValue("ON TOP");
        headerRow2.getCell(c+20).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 2, c+20, c+20, workbook, sheet);
        headerRow3.createCell(c+20).setCellValue("\u0E2A\u0E1B\u0E2A\u0E0A.\u0E08\u0E48\u0E32\u0E22");
        headerRow3.getCell(c+20).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 3, rowNumber + 3, c+20, c+20, workbook, sheet);
        headerRow2.createCell(c+21).setCellValue("\u0E08\u0E48\u0E32\u0E22\u0E0A\u0E14\u0E40\u0E0A\u0E22");
        headerRow2.getCell(c+21).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 3, c+21, c+21, workbook, sheet);
        ///////////////////////////////////////////////////////////////////
        
        headerRow2.createCell(c+22).setCellValue("\u0E08\u0E48\u0E32\u0E22\u0E40\u0E1E\u0E34\u0E48\u0E21");
        headerRow2.getCell(c+22).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 3, c+22, c+22, workbook, sheet);
        headerRow2.createCell(c+23).setCellValue("\u0E40\u0E23\u0E35\u0E22\u0E01\u0E04\u0E37\u0E19");
        headerRow2.getCell(c+23).setCellStyle(styleHeaderCenter);
        this.buildMergeAndBorder(rowNumber + 2, rowNumber + 3, c+23, c+23, workbook, sheet);
        
        // create data rows
        int rowCount = rowNumber+4;
	    int cnt = 1;
	    
		BigDecimal totalSalaryAmount = new BigDecimal(0);
		BigDecimal totalDischarge = new BigDecimal(0);
		BigDecimal totalChargeRule = new BigDecimal(0);
		BigDecimal totalCompensation = new BigDecimal(0);
		BigDecimal totalOrfHMain = new BigDecimal(0);
		BigDecimal totalOrfNHSO = new BigDecimal(0);
		
		BigDecimal totalSumHC = new BigDecimal(0);
		BigDecimal totalSumAE = new BigDecimal(0);
		BigDecimal totalSumINST = new BigDecimal(0);
		BigDecimal totalSumIP = new BigDecimal(0);
		BigDecimal totalSumDMIS = new BigDecimal(0);
		BigDecimal totalSumOP = new BigDecimal(0);
		BigDecimal totalSumPA = new BigDecimal(0);
		BigDecimal totalSumOT = new BigDecimal(0);
		BigDecimal totalSumDRUG = new BigDecimal(0);
		
		BigDecimal totalOldTotal = new BigDecimal(0);
		BigDecimal totalOldSumHC = new BigDecimal(0);
		BigDecimal totalOldSumAE = new BigDecimal(0);
		BigDecimal totalOldSumINST = new BigDecimal(0);
		BigDecimal totalOldSumIP = new BigDecimal(0);
		BigDecimal totalOldSumDMIS = new BigDecimal(0);
		BigDecimal totalOldSumOP = new BigDecimal(0);
		BigDecimal totalOldSumPA = new BigDecimal(0);
		BigDecimal totalOldSumOT = new BigDecimal(0);
		BigDecimal totalOldSumDRUG = new BigDecimal(0);
		BigDecimal totalOldAmount = new BigDecimal(0);
		
		BigDecimal totalNewSumHC = new BigDecimal(0);
		BigDecimal totalNewSumAE = new BigDecimal(0);
		BigDecimal totalNewSumINST = new BigDecimal(0);
		BigDecimal totalNewSumIP = new BigDecimal(0);
		BigDecimal totalNewSumDMIS = new BigDecimal(0);
		BigDecimal totalNewSumOP = new BigDecimal(0);
		BigDecimal totalNewSumPA = new BigDecimal(0);
		BigDecimal totalNewSumOT = new BigDecimal(0);
		BigDecimal totalNewSumDRUG = new BigDecimal(0);
		BigDecimal totalNewDischarge = new BigDecimal(0);
		BigDecimal totalNewCompensation = new BigDecimal(0);
		
		BigDecimal totalPay = new BigDecimal(0);
		BigDecimal totalReturn = new BigDecimal(0);
		
		int countStatusA = 0;
        for (final SummaryRepDataBean b : listSummaryDetails) {
            final HSSFRow dataRow = sheet.createRow(rowCount);
            int column = 0;
//            final int cntORF = b.getCntORF();
            dataRow.createCell(column).setCellValue(cnt++);
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(b.getHospitalCode());
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(StringUtils.nullToEmpty(b.getTranId()));
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(StringUtils.nullToEmpty(b.getEclaimNo()));
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(StringUtils.nullToEmpty(b.getHn()));
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(StringUtils.nullToEmpty(b.getAn()));
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(StringUtils.nullToEmpty(b.getPid()));
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(StringUtils.nullToEmpty(b.getPersonType()));
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(StringUtils.nullToEmpty(b.getFullname()));
            dataRow.getCell(column++).setCellStyle(styleDataLeft);
            dataRow.createCell(column).setCellValue(StringUtils.checkDate(b.getAdmidDate()) + " " + StringUtils.nullToEmpty(b.getAdmidTime()));
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(StringUtils.checkDate(b.getDischargeDate()) + " " + StringUtils.nullToEmpty(b.getDischargeTime()));
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            
            String salaryPercent = StringUtils.zeroToEmpty(StringUtils.nullToEmpty(String.valueOf(b.getSalaryPercent())));
            salaryPercent = salaryPercent.equalsIgnoreCase("") ? "-" : salaryPercent;
            dataRow.createCell(column).setCellValue(salaryPercent);
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(b.getSalaryAmount().doubleValue());
            dataRow.getCell(column++).setCellStyle(styleDataRight);
            dataRow.createCell(column).setCellValue(b.getDischarge().doubleValue());
            dataRow.getCell(column++).setCellStyle(styleDataRight);

			final String codeDWHC = StringUtils.nullToEmpty(b.getCodeDWHC());
			final String codeDWINST = StringUtils.nullToEmpty(b.getCodeDWINST());
			final String codeDWAE = StringUtils.nullToEmpty(b.getCodeDWAE());
			final String codeDWIP = StringUtils.nullToEmpty(b.getCodeDWIP());
			final String codeDWDMIS = StringUtils.nullToEmpty(b.getCodeDWDMIS());
			final BigDecimal sumsHC = b.getSumsHC();
			final BigDecimal sumsAE = b.getSumsAE();
			final BigDecimal sumsINST = b.getSumsINST();
			final BigDecimal sumsIP = b.getSumsIP();
			final BigDecimal sumsDMIS = b.getSumsDMIS();
			final BigDecimal sumsDRUG = b.getSumsDRUG();
			
			final String codeDWIPCSLG = StringUtils.nullToEmpty(b.getCodeDWIPCSLG());
			final String codeDWOPCSLG = StringUtils.nullToEmpty(b.getCodeDWOPCSLG());
			final String codePACSLG = StringUtils.nullToEmpty(b.getCodePACSLG());
			final String codeINSTCSLG = StringUtils.nullToEmpty(b.getCodeINSTCSLG());
			final String codeOTCSLG = StringUtils.nullToEmpty(b.getCodeDWOPCSLG());
			final BigDecimal sumsIPCS = b.getSumsIPCS();
			final BigDecimal sumsOPCS = b.getSumsOPCS();
			final BigDecimal sumsPACS = b.getSumsPACS();
			final BigDecimal sumsINSTCS = b.getSumsINSTCS();
			final BigDecimal sumsOTCS = b.getSumsOTCS();
			final BigDecimal sumsDRUGCS = b.getSumsDRUGCS();
			
            String status = "\u0E1C\u0E48\u0E32\u0E19";
			
			if(b.getStatus().equalsIgnoreCase("A")){
				status = "\u0E2D\u0E38\u0E17\u0E18\u0E23\u0E13\u0E4C";
			}else if(b.getStatus().equalsIgnoreCase("ND")){
				String statusDeny = "";
				
				if(!codeDWHC.equalsIgnoreCase("") && !codeDWHC.equalsIgnoreCase("C")&& !codeDWHC.equalsIgnoreCase("N")){
					statusDeny += codeDWHC;
				}
				if(!codeDWINST.equalsIgnoreCase("") && !codeDWINST.equalsIgnoreCase("C")&& !codeDWINST.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codeDWINST;
				}
				if(!codeDWAE.equalsIgnoreCase("") && !codeDWAE.equalsIgnoreCase("C")&& !codeDWAE.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codeDWAE;
				}
				if(!codeDWIP.equalsIgnoreCase("") && !codeDWIP.equalsIgnoreCase("C")&& !codeDWIP.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codeDWIP;
				}
				if(!codeDWDMIS.equalsIgnoreCase("") && !codeDWDMIS.equalsIgnoreCase("C")&& !codeDWDMIS.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codeDWDMIS;
				}
				if(!codeDWIPCSLG.equalsIgnoreCase("") && !codeDWIPCSLG.equalsIgnoreCase("C")&& !codeDWIPCSLG.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codeDWIPCSLG;
				}
				if(!codeDWOPCSLG.equalsIgnoreCase("") && !codeDWOPCSLG.equalsIgnoreCase("C")&& !codeDWOPCSLG.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codeDWOPCSLG;
				}
				if(!codePACSLG.equalsIgnoreCase("") && !codePACSLG.equalsIgnoreCase("C")&& !codePACSLG.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codePACSLG;
				}
				if(!codeINSTCSLG.equalsIgnoreCase("") && !codeINSTCSLG.equalsIgnoreCase("C")&& !codeINSTCSLG.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codeINSTCSLG;
				}
				if(!codeOTCSLG.equalsIgnoreCase("") && !codeOTCSLG.equalsIgnoreCase("C")&& !codeOTCSLG.equalsIgnoreCase("N")){
					if(!statusDeny.equalsIgnoreCase("")){
						statusDeny += ", ";
					}
					statusDeny += codeOTCSLG;
				}
				
				status = "\u0E44\u0E21\u0E48\u0E40\u0E02\u0E49\u0E32\u0E40\u0E01\u0E13\u0E11\u0E4C (" + statusDeny + ")";
			}else if(b.getStatus().equalsIgnoreCase("N")){
				String statusDeny = "";
				String[] tmps = b.getCodeId().split(",");
				for(int j=0; j<tmps.length; j++){
					if(j == 0){
						statusDeny += "C" + tmps[j];
					}else{
						statusDeny += ", C" + tmps[j];
					}
				}
				status = "\u0E44\u0E21\u0E48\u0E1C\u0E48\u0E32\u0E19 (" + statusDeny + ")";
			}
			
			if(b.getOrf().equalsIgnoreCase("Y")){
				status += " (ORF)*";
			}
            
			BigDecimal totalDisCom = b.getDischarge().subtract(b.getCompensation());
			
            dataRow.createCell(column).setCellValue(status);
            dataRow.getCell(column++).setCellStyle(styleDataCenter);
            dataRow.createCell(column).setCellValue(b.getChargeRule().doubleValue());
            dataRow.getCell(column++).setCellStyle(styleDataRight);
            dataRow.createCell(column).setCellValue(b.getCompensation().doubleValue());
            dataRow.getCell(column++).setCellStyle(styleDataRight);
            dataRow.createCell(column).setCellValue(totalDisCom.doubleValue());
            dataRow.getCell(column++).setCellStyle(styleDataRight);
            dataRow.createCell(column).setCellValue(b.getOrfHMain().doubleValue());
            dataRow.getCell(column++).setCellStyle(styleDataRight);
            dataRow.createCell(column).setCellValue(b.getOrfNHSO().doubleValue());
            dataRow.getCell(column++).setCellStyle(styleDataRight);
            
            String value = "";
            boolean isNotPass = false;
            if(codeDWHC.equalsIgnoreCase("C")){
            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsHC));
				if(!value.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsHC.doubleValue());
					totalSumHC = totalSumHC.add(sumsHC);
				}else{
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}
			}else if(codeDWHC.equalsIgnoreCase("N")){
				dataRow.createCell(column).setCellValue("");
				isNotPass = true;
			}else{
				dataRow.createCell(column).setCellValue(codeDWHC);
				isNotPass = true;
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

            isNotPass = false;
            if(codeDWAE.equalsIgnoreCase("C")){
            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsAE));
				if(!value.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsAE.doubleValue());
					totalSumAE = totalSumAE.add(sumsAE);
				}else{
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}
			}else if(codeDWAE.equalsIgnoreCase("N")){
				dataRow.createCell(column).setCellValue("");
				isNotPass = true;
			}else{
				dataRow.createCell(column).setCellValue(codeDWAE);
				isNotPass = true;
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

            isNotPass = false;
            if(codeDWINST.equalsIgnoreCase("C")){
            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsINST));
				if(!value.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsINST.doubleValue());
					totalSumINST = totalSumINST.add(sumsINST);
				}else{
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}
			}else if(codeDWINST.equalsIgnoreCase("N")){
				dataRow.createCell(column).setCellValue("");
				isNotPass = true;
			}else if(!codeDWINST.equalsIgnoreCase("")){
				dataRow.createCell(column).setCellValue(codeDWINST);
				isNotPass = true;
			}else{
				if(codeINSTCSLG.equalsIgnoreCase("C")){
					value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsINSTCS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsINSTCS.doubleValue());
						totalSumINST = totalSumINST.add(sumsINSTCS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeINSTCSLG.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(codeINSTCSLG);
					isNotPass = true;
				}
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
			
            isNotPass = false;
            if(codeDWIP.equalsIgnoreCase("C")){
            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsIP));
				if(!value.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsIP.doubleValue());
					totalSumIP = totalSumIP.add(sumsIP);
				}else{
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}
			}else if(codeDWIP.equalsIgnoreCase("N")){
				dataRow.createCell(column).setCellValue("");
				isNotPass = true;
			}else if(!codeDWIP.equalsIgnoreCase("")){
				dataRow.createCell(column).setCellValue(codeDWIP);
				isNotPass = true;
			}else{
				if(codeDWIPCSLG.equalsIgnoreCase("C")){
					value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsIPCS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsIPCS.doubleValue());
						totalSumIP = totalSumIP.add(sumsIPCS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeDWIPCSLG.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(codeDWIPCSLG);
					isNotPass = true;
				}
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

            isNotPass = false;
            if(codeDWDMIS.equalsIgnoreCase("C")){
            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsDMIS));
				if(!value.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsDMIS.doubleValue());
					totalSumDMIS = totalSumDMIS.add(sumsDMIS);
				}else{
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}
			}else if(codeDWDMIS.equalsIgnoreCase("N")){
				dataRow.createCell(column).setCellValue("");
				isNotPass = true;
			}else{
				dataRow.createCell(column).setCellValue(codeDWDMIS);
				isNotPass = true;
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

            isNotPass = false;
            if(codeDWOPCSLG.equalsIgnoreCase("C")){
            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsOPCS));
				if(!value.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsOPCS.doubleValue());
					totalSumOP = totalSumOP.add(sumsOPCS);
				}else{
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}
			}else if(codeDWOPCSLG.equalsIgnoreCase("N")){
				dataRow.createCell(column).setCellValue("");
				isNotPass = true;
			}else{
				dataRow.createCell(column).setCellValue(codeDWOPCSLG);
				isNotPass = true;
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

            isNotPass = false;
            if(codePACSLG.equalsIgnoreCase("C")){
            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsPACS));
				if(!value.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsPACS.doubleValue());
					totalSumPA = totalSumPA.add(sumsPACS);
				}else{
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}
			}else if(codePACSLG.equalsIgnoreCase("N")){
				dataRow.createCell(column).setCellValue("");
				isNotPass = true;
			}else{
				dataRow.createCell(column).setCellValue(codePACSLG);
				isNotPass = true;
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
			
            isNotPass = false;
            String valueDrug = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsDRUG));
            String valueDrugCS = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsDRUGCS));
			if(!valueDrug.equalsIgnoreCase("")){
				dataRow.createCell(column).setCellValue(sumsDRUG.doubleValue());
				totalSumDRUG = totalSumDRUG.add(sumsDRUG);
			}else if(!valueDrugCS.equalsIgnoreCase("")){
				dataRow.createCell(column).setCellValue(sumsDRUGCS.doubleValue());
				totalSumDRUG = totalSumDRUG.add(sumsDRUGCS);
			}else{
				isNotPass = true;
				dataRow.createCell(column).setCellValue("");
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

            isNotPass = false;
            if(codeOTCSLG.equalsIgnoreCase("C")){
            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsOTCS));
				if(!value.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsOTCS.doubleValue());
					totalSumOT = totalSumOT.add(sumsOTCS);
				}else{
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}
			}else if(codeOTCSLG.equalsIgnoreCase("N")){
				dataRow.createCell(column).setCellValue("");
				isNotPass = true;
			}else{
				dataRow.createCell(column).setCellValue(codeOTCSLG);
				isNotPass = true;
			}
            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
            
            totalSalaryAmount = totalSalaryAmount.add(b.getSalaryAmount());
            totalDischarge = totalDischarge.add(b.getDischarge());
            totalChargeRule = totalChargeRule.add(b.getChargeRule());
            totalCompensation = totalCompensation.add(b.getCompensation());
            
            totalOrfHMain = totalOrfHMain.add(b.getOrfHMain());
            totalOrfNHSO = totalOrfNHSO.add(b.getOrfNHSO());
			
			
            if(b.getStatus().equalsIgnoreCase("A")){
            	countStatusA++;

				final String oldCodeDWHC = StringUtils.nullToEmpty(b.getOldCodeDWHC());
				final String oldCodeDWINST = StringUtils.nullToEmpty(b.getOldCodeDWINST());
				final String oldCodeDWAE = StringUtils.nullToEmpty(b.getOldCodeDWAE());
				final String oldCodeDWIP = StringUtils.nullToEmpty(b.getOldCodeDWIP());
				final String oldCodeDWDMIS = StringUtils.nullToEmpty(b.getOldCodeDWDMIS());
				final BigDecimal oldSumsHC = b.getOldSumsHC();
				final BigDecimal oldSumsAE = b.getOldSumsAE();
				final BigDecimal oldSumsINST = b.getOldSumsINST();
				final BigDecimal oldSumsIP = b.getOldSumsIP();
				final BigDecimal oldSumsDMIS = b.getOldSumsDMIS();
				final BigDecimal oldSumsDRUG = b.getOldSumsDRUG();
				
				final String oldCodeDWIPCSLG = StringUtils.nullToEmpty(b.getOldCodeDWIPCSLG());
				final String oldCodeDWOPCSLG = StringUtils.nullToEmpty(b.getOldCodeDWOPCSLG());
				final String oldCodeDWPACSLG = StringUtils.nullToEmpty(b.getOldCodeDWPACSLG());
				final String oldCodeDWINSTCSLG = StringUtils.nullToEmpty(b.getOldCodeDWINSTCSLG());
				final String oldCodeDWOTCSLG = StringUtils.nullToEmpty(b.getOldCodeDWOTCSLG());
				final BigDecimal oldSumsIPCS = b.getOldSumsIPCS();
				final BigDecimal oldSumsOPCS = b.getOldSumsOPCS();
				final BigDecimal oldSumsPACS = b.getOldSumsPACS();
				final BigDecimal oldSumsINSTCS = b.getOldSumsINSTCS();
				final BigDecimal oldSumsOTCS = b.getOldSumsOTCS();
				final BigDecimal oldSumsDRUGCS = b.getOldSumsDRUGCS();          	
            	
            	totalOldTotal = totalOldTotal.add(b.getOldDischarge());
            	totalOldAmount = totalOldAmount.add(b.getOldCompensation());

            	totalNewDischarge = totalNewDischarge.add(b.getDischarge());
            	totalNewCompensation = totalNewCompensation.add(b.getCompensation());
				
				/// Old Appeal
	            dataRow.createCell(column).setCellValue(b.getOldDischarge().doubleValue());
	            dataRow.getCell(column++).setCellStyle(styleDataRight);
	            isNotPass = false;
	            if(oldCodeDWHC.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsHC));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(oldSumsHC.doubleValue());
						totalOldSumHC = totalOldSumHC.add(oldSumsHC);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(oldCodeDWHC.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(oldCodeDWHC);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(oldCodeDWAE.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsAE));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(oldSumsAE.doubleValue());
						totalOldSumAE = totalOldSumAE.add(oldSumsAE);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(oldCodeDWAE.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(oldCodeDWAE);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(oldCodeDWINST.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsINST));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(oldSumsINST.doubleValue());
						totalOldSumINST = totalOldSumINST.add(oldSumsINST);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(oldCodeDWINST.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else if(!oldCodeDWINST.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(oldCodeDWINST);
					isNotPass = true;
				}else{
					if(oldCodeDWINSTCSLG.equalsIgnoreCase("C")){
						value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsINSTCS));
						if(!value.equalsIgnoreCase("")){
							dataRow.createCell(column).setCellValue(oldSumsINSTCS.doubleValue());
							totalOldSumINST = totalOldSumINST.add(oldSumsINSTCS);
						}else{
							dataRow.createCell(column).setCellValue("");
							isNotPass = true;
						}
					}else if(oldCodeDWINSTCSLG.equalsIgnoreCase("N")){
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}else{
						dataRow.createCell(column).setCellValue(oldCodeDWINSTCSLG);
						isNotPass = true;
					}
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
				
	            isNotPass = false;
	            if(oldCodeDWIP.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsIP));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(oldSumsIP.doubleValue());
						totalOldSumIP = totalOldSumIP.add(oldSumsIP);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(oldCodeDWIP.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else if(!oldCodeDWIP.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(oldCodeDWIP);
					isNotPass = true;
				}else{
					if(oldCodeDWIPCSLG.equalsIgnoreCase("C")){
						value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsIPCS));
						if(!value.equalsIgnoreCase("")){
							dataRow.createCell(column).setCellValue(oldSumsIPCS.doubleValue());
							totalOldSumIP = totalOldSumIP.add(oldSumsIPCS);
						}else{
							dataRow.createCell(column).setCellValue("");
							isNotPass = true;
						}
					}else if(oldCodeDWIPCSLG.equalsIgnoreCase("N")){
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}else{
						dataRow.createCell(column).setCellValue(oldCodeDWIPCSLG);
						isNotPass = true;
					}
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(oldCodeDWDMIS.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsDMIS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(oldSumsDMIS.doubleValue());
						totalOldSumDMIS = totalOldSumDMIS.add(oldSumsDMIS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(oldCodeDWDMIS.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(oldCodeDWDMIS);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(oldCodeDWOPCSLG.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsOPCS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(oldSumsOPCS.doubleValue());
						totalOldSumOP = totalOldSumOP.add(oldSumsOPCS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(oldCodeDWOPCSLG.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(oldCodeDWOPCSLG);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(oldCodeDWPACSLG.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsPACS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(oldSumsPACS.doubleValue());
						totalOldSumPA = totalOldSumPA.add(oldSumsPACS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(oldCodeDWPACSLG.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(oldCodeDWPACSLG);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
				
	            isNotPass = false;
	            valueDrug = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsDRUG));
	            valueDrugCS = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsDRUGCS));
				if(!valueDrug.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(oldSumsDRUG.doubleValue());
					totalOldSumDRUG = totalOldSumDRUG.add(oldSumsDRUG);
				}else if(!valueDrugCS.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(oldSumsDRUGCS.doubleValue());
					totalOldSumDRUG = totalOldSumDRUG.add(oldSumsDRUGCS);
				}else{
					isNotPass = true;
					dataRow.createCell(column).setCellValue("");
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(oldCodeDWOTCSLG.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(oldSumsOTCS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(oldSumsOTCS.doubleValue());
						totalOldSumOT = totalOldSumOT.add(oldSumsOTCS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(oldCodeDWOTCSLG.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(oldCodeDWOTCSLG);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
	            
	            dataRow.createCell(column).setCellValue(b.getOldCompensation().doubleValue());
	            dataRow.getCell(column++).setCellStyle(styleDataRight);
				////////////////////////////////////////////////////////////
	            
	            // New Appeal
	            dataRow.createCell(column).setCellValue(b.getDischarge().doubleValue());	            
	            dataRow.getCell(column++).setCellStyle(styleDataRight);	            
	            isNotPass = false;
	            if(codeDWHC.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsHC));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsHC.doubleValue());
						totalNewSumHC = totalNewSumHC.add(sumsHC);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeDWHC.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(codeDWHC);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(codeDWAE.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsAE));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsAE.doubleValue());
						totalNewSumAE = totalNewSumAE.add(sumsAE);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeDWAE.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(codeDWAE);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(codeDWINST.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsINST));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsINST.doubleValue());
						totalNewSumINST = totalNewSumINST.add(sumsINST);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeDWINST.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else if(!codeDWINST.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(codeDWINST);
					isNotPass = true;
				}else{
					if(codeINSTCSLG.equalsIgnoreCase("C")){
						value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsINSTCS));
						if(!value.equalsIgnoreCase("")){
							dataRow.createCell(column).setCellValue(sumsINSTCS.doubleValue());
							totalNewSumINST = totalNewSumINST.add(sumsINSTCS);
						}else{
							dataRow.createCell(column).setCellValue("");
							isNotPass = true;
						}
					}else if(codeINSTCSLG.equalsIgnoreCase("N")){
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}else{
						dataRow.createCell(column).setCellValue(codeINSTCSLG);
						isNotPass = true;
					}
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
				
	            isNotPass = false;
	            if(codeDWIP.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsIP));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsIP.doubleValue());
						totalNewSumIP = totalNewSumIP.add(sumsIP);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeDWIP.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else if(!codeDWIP.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(codeDWIP);
					isNotPass = true;
				}else{
					if(codeDWIPCSLG.equalsIgnoreCase("C")){
						value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsIPCS));
						if(!value.equalsIgnoreCase("")){
							dataRow.createCell(column).setCellValue(sumsIPCS.doubleValue());
							totalNewSumIP = totalNewSumIP.add(sumsIPCS);
						}else{
							dataRow.createCell(column).setCellValue("");
							isNotPass = true;
						}
					}else if(codeDWIPCSLG.equalsIgnoreCase("N")){
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}else{
						dataRow.createCell(column).setCellValue(codeDWIPCSLG);
						isNotPass = true;
					}
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(codeDWDMIS.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsDMIS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsDMIS.doubleValue());
						totalNewSumDMIS = totalNewSumDMIS.add(sumsDMIS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeDWDMIS.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(codeDWDMIS);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(codeDWOPCSLG.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsOPCS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsOPCS.doubleValue());
						totalNewSumOP = totalNewSumOP.add(sumsOPCS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeDWOPCSLG.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(codeDWOPCSLG);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(codePACSLG.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsPACS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsPACS.doubleValue());
						totalNewSumPA = totalNewSumPA.add(sumsPACS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codePACSLG.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(codePACSLG);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
				
	            isNotPass = false;
	            valueDrug = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsDRUG));
	            valueDrugCS = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsDRUGCS));
				if(!valueDrug.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsDRUG.doubleValue());
					totalNewSumDRUG = totalNewSumDRUG.add(sumsDRUG);
				}else if(!valueDrugCS.equalsIgnoreCase("")){
					dataRow.createCell(column).setCellValue(sumsDRUGCS.doubleValue());
					totalNewSumDRUG = totalNewSumDRUG.add(sumsDRUGCS);
				}else{
					isNotPass = true;
					dataRow.createCell(column).setCellValue("");
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);

	            isNotPass = false;
	            if(codeOTCSLG.equalsIgnoreCase("C")){
	            	value = StringUtils.zeroToEmpty(StringUtils.currencyFormat(sumsOTCS));
					if(!value.equalsIgnoreCase("")){
						dataRow.createCell(column).setCellValue(sumsOTCS.doubleValue());
						totalNewSumOT = totalNewSumOT.add(sumsOTCS);
					}else{
						dataRow.createCell(column).setCellValue("");
						isNotPass = true;
					}
				}else if(codeOTCSLG.equalsIgnoreCase("N")){
					dataRow.createCell(column).setCellValue("");
					isNotPass = true;
				}else{
					dataRow.createCell(column).setCellValue(codeOTCSLG);
					isNotPass = true;
				}
	            dataRow.getCell(column++).setCellStyle(isNotPass ? styleDataCenter : styleDataRight);
	            
	            dataRow.createCell(column).setCellValue(b.getCompensation().doubleValue());
	            dataRow.getCell(column++).setCellStyle(styleDataRight);
	            //**************************************************************************************//
	            
				BigDecimal amnt = b.getOldCompensation().subtract(b.getCompensation());
				BigDecimal pay = new BigDecimal(0);
				BigDecimal payReturn = new BigDecimal(0);
				if(amnt.doubleValue() < 0){
					pay = b.getCompensation().subtract(b.getCompensation());
					payReturn = new BigDecimal(0);
				}else{
					pay = new BigDecimal(0);
					payReturn = amnt;
				}

	            dataRow.createCell(column).setCellValue(pay.doubleValue());
	            dataRow.getCell(column++).setCellStyle(styleDataRight);
	            dataRow.createCell(column).setCellValue(payReturn.doubleValue());
	            dataRow.getCell(column++).setCellStyle(styleDataRight);
            }else{
            	int subCol = column++;
                dataRow.createCell(subCol).setCellValue("\u0E44\u0E21\u0E48\u0E21\u0E35\u0E2D\u0E38\u0E17\u0E18\u0E23\u0E13\u0E4C");
                dataRow.getCell(subCol).setCellStyle(styleDataCenter);
                this.buildMergeAndBorder(rowCount, rowCount, subCol, subCol+10, workbook, sheet);
                column += 10;
                subCol = column;
                dataRow.createCell(subCol).setCellValue("\u0E44\u0E21\u0E48\u0E21\u0E35\u0E2D\u0E38\u0E17\u0E18\u0E23\u0E13\u0E4C");
                dataRow.getCell(subCol).setCellStyle(styleDataCenter);
                this.buildMergeAndBorder(rowCount, rowCount, subCol, subCol+10, workbook, sheet);
                column += 11;
                subCol = column;
                dataRow.createCell(subCol).setCellValue("");
                dataRow.getCell(subCol).setCellStyle(styleDataCenter);
                subCol = ++column;
                dataRow.createCell(subCol).setCellValue("");
                dataRow.getCell(subCol).setCellStyle(styleDataCenter);
            }
            
            
            rowCount++;
        }

        final CellStyle totalStyleCenter = this.styleCenterWithBorderBGGrey(workbook);
        final CellStyle totalStyleRight = this.styleCenterWithBorderBGGrey(workbook);
        totalStyleRight.setAlignment(CellStyle.ALIGN_RIGHT);
        totalStyleRight.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
        
        final HSSFRow totalRow = sheet.createRow(rowCount);
		int column = 0;

		totalRow.createCell(column).setCellValue("\u0E23\u0E27\u0E21");
		totalRow.getCell(column).setCellStyle(totalStyleCenter);
		this.buildMergeAndBorder(rowCount, rowCount, column, column+10, workbook, sheet);
		column += 10;
		column++;
		
		totalRow.createCell(column).setCellValue("");
		totalRow.getCell(column++).setCellStyle(totalStyleCenter);
		totalRow.createCell(column).setCellValue(totalSalaryAmount.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);
		
		totalRow.createCell(column).setCellValue(totalDischarge.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);
		
		totalRow.createCell(column).setCellValue("");
		totalRow.getCell(column++).setCellStyle(totalStyleCenter);

		totalRow.createCell(column).setCellValue(totalChargeRule.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalCompensation.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalDischarge.subtract(totalCompensation).doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalOrfHMain.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalOrfNHSO.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumHC.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumAE.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumINST.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumIP.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumDMIS.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumOP.doubleValue());		
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumPA.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumDRUG.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);

		totalRow.createCell(column).setCellValue(totalSumOT.doubleValue());
		totalRow.getCell(column++).setCellStyle(totalStyleRight);
		
		if(countStatusA > 0){
			//*************************** total old appeal ***************************/
			totalRow.createCell(column).setCellValue(totalOldTotal.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumHC.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumAE.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumINST.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumIP.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumDMIS.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumOP.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumPA.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumDRUG.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldSumOT.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalOldAmount.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);
			//******************************************************************************************//
			
			//********************************* total new appeal ******************************************//
			totalRow.createCell(column).setCellValue(totalNewDischarge.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumHC.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumAE.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumINST.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumIP.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumDMIS.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumOP.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumPA.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumDRUG.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewSumOT.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);

			totalRow.createCell(column).setCellValue(totalNewCompensation.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);
			//******************************************************************************************//
			
			BigDecimal tt = totalOldAmount.subtract(totalNewCompensation);
			if(tt.doubleValue() < 0){
				totalReturn = totalNewCompensation.subtract(totalOldAmount);
				totalPay = new BigDecimal(0);
			}else{
				totalReturn = new BigDecimal(0);
				totalPay = tt;
			}

			totalRow.createCell(column).setCellValue(totalReturn.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);
			totalRow.createCell(column).setCellValue(totalPay.doubleValue());
			totalRow.getCell(column++).setCellStyle(totalStyleRight);
		}else{
        	int subCol = column++;
        	totalRow.createCell(subCol).setCellValue("\u0E44\u0E21\u0E48\u0E21\u0E35\u0E2D\u0E38\u0E17\u0E18\u0E23\u0E13\u0E4C");
            totalRow.getCell(subCol).setCellStyle(totalStyleCenter);
            this.buildMergeAndBorder(rowCount, rowCount, subCol, subCol+10, workbook, sheet);
            column += 10;
            subCol = column;
            totalRow.createCell(subCol).setCellValue("\u0E44\u0E21\u0E48\u0E21\u0E35\u0E2D\u0E38\u0E17\u0E18\u0E23\u0E13\u0E4C");
            totalRow.getCell(subCol).setCellStyle(totalStyleCenter);
            this.buildMergeAndBorder(rowCount, rowCount, subCol, subCol+10, workbook, sheet);
            column += 11;
            subCol = column;
            totalRow.createCell(subCol).setCellValue("");
            totalRow.getCell(subCol).setCellStyle(totalStyleCenter);
            subCol = ++column;
            totalRow.createCell(subCol).setCellValue("");
            totalRow.getCell(subCol).setCellStyle(totalStyleCenter);
		}
	}
	
	private void buildMergeAndBorder(final int startRow, final int lastRow, final int startColumn, final int lastColumn, final HSSFWorkbook workbook, final HSSFSheet sheet){
        final short border = CellStyle.BORDER_THIN;
        final short borderColor = HSSFColor.BLACK.index;
        
        CellRangeAddress region = new CellRangeAddress(
        		startRow, //first row (0-based)
                lastRow, //last row  (0-based)
                startColumn, //first column (0-based)
                lastColumn  //last column  (0-based)
        );
        sheet.addMergedRegion(region);
        
        RegionUtil.setBorderBottom(border, region, sheet, workbook );
        RegionUtil.setBorderTop(border, region, sheet, workbook );
        RegionUtil.setBorderLeft(border, region, sheet, workbook );
        RegionUtil.setBorderRight(border, region, sheet, workbook );
        RegionUtil.setBottomBorderColor(borderColor, region, sheet, workbook);
        RegionUtil.setTopBorderColor(borderColor, region, sheet, workbook);
        RegionUtil.setLeftBorderColor(borderColor, region, sheet, workbook);
        RegionUtil.setRightBorderColor(borderColor, region, sheet, workbook);
	}
	
	private CellStyle styleCenterWithBorderHeader(final HSSFWorkbook workbook){
		final CellStyle style = workbook.createCellStyle();
		final Font font = workbook.createFont();
        font.setFontName("Tahoma");
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderBottom((short)1);
        style.setBorderTop((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        
        return style;
	}
	
	private CellStyle styleCenterWithBorder(final HSSFWorkbook workbook){
		final CellStyle style = workbook.createCellStyle();
		final Font font = workbook.createFont();
        font.setFontName("Tahoma");
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom((short)1);
        style.setBorderTop((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setFont(font);
                
		return style;
	}
	
	private CellStyle styleRightWithBorder(final HSSFWorkbook workbook){
		final CellStyle style = workbook.createCellStyle();
        final Font font = workbook.createFont();
        font.setFontName("Tahoma");
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom((short)1);
        style.setBorderTop((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
                
		return style;
	}
	
	private CellStyle styleLeftWithBorder(final HSSFWorkbook workbook){
        final CellStyle style = workbook.createCellStyle();
        final Font font = workbook.createFont();
        font.setFontName("Tahoma");
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom((short)1);
        style.setBorderTop((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setFont(font);
                
		return style;
	}
	
	private CellStyle styleCenterWithBorderBGGrey(final HSSFWorkbook workbook){
		final CellStyle style = workbook.createCellStyle();
		final Font font = workbook.createFont();
        font.setFontName("Tahoma");
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderBottom((short)1);
        style.setBorderTop((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        
        return style;
	}
}
